﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using MPMovieCatalog.Domain;

namespace MPMovieCatalog.DAO
{
    class MovieDAO
    {

        private const string MOVIE_SHORT_INFO_COMMAND = "select idMovie, strTitle from movieinfo mi";

        private const string MOVIE_INFO_COMMAND = "select mi.*, a.strActor as strDirector from movieinfo mi left join actors a on a.idActor = mi.idDirector where mi.idMovie = @movieId";

        private const string MOVIE_ACTORS_COMMAND = "select a.* from movieinfo mi, actors a, actorlinkmovie alm where a.idActor = alm.idActor and alm.idMovie = mi.idMovie and alm.idActor != mi.idDirector and mi.idMovie = @movieId order by a.strActor";

        private const string MOVIE_GENRES_COMMAND = "select g.* from genre g, genrelinkmovie glm where g.idGenre = glm.idGenre and glm.idMovie = @movieId order by g.strGenre";

        private const string UPDATE_MOVIE_INFO_COMMAND = "update movieinfo set strTitle = @title, strTagLine = @tagline, idDirector = @directorId, strCredits = @writer, iYear = @year, runtime = @runtime, strPlot = @description, strPlotOutLine = @description, fRating = @imdbRating, IMDBID = @imdbNumber, strVotes = @imdbVotes, mpaa = @mpaaRating where idMovie = @movieId";

        private const string AVAIL_ACTORS_COMMAND = "select a.* from actors a where a.idActor not in (select alm.idActor from movieinfo mi, actorlinkmovie alm where alm.idMovie = mi.idMovie and alm.idActor != mi.idDirector and mi.idMovie = @movieId)";

        private const string AVAIL_GENRES_COMMAND = "select g.* from genre g where g.idGenre not in (select idGenre from genrelinkmovie glm where glm.idMovie = @movieId) order by g.strGenre";

        private const string DELETE_MOVIE_ACTORS_COMMAND = "delete from actorlinkmovie where idMovie = @movieId";

        private const string DELETE_MOVIE_GENRES_COMMAND = "delete from genrelinkmovie where idMovie = @movieId";

        private const string INSERT_MOVIE_ACTOR_COMMAND = "insert into actorlinkmovie(idMovie, idActor) values (@movieId, @actorId)";

        private const string INSERT_MOVIE_GENRE_COMMAND = "insert into genrelinkmovie(idMovie, idGenre) values (@movieId, @genreId)";

        private const string INSERT_ACTOR_COMMAND = "insert into actors(idActor, strActor) values (NULL, @name); select last_insert_rowid() as [id]";

        private const string INSERT_GENRE_COMMAND = "insert into genre(idGenre, strGenre) values (NULL, @name); select last_insert_rowid() as [id]";

        private const string FIND_ACTOR_BY_NAME_COMMAND = "select idActor from actors where strActor = @name";

        private const string FIND_GENRE_BY_NAME_COMMAND = "select idGenre from genre where strGenre = @name";


        public static List<MovieShortInfo> FindAllMoviesShortInfo()
        {
            List<MovieShortInfo> movies = new List<MovieShortInfo>();
            using (SQLiteConnection con = new SQLiteConnection(ConnectionUtil.ConnectionString))
            {
                con.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(MOVIE_SHORT_INFO_COMMAND, con))
                {
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                MovieShortInfo movieShortInfo = new MovieShortInfo();
                                movieShortInfo.Id = long.Parse(reader["idMovie"].ToString());
                                movieShortInfo.Name = reader["strTitle"].ToString();

                                movies.Add(movieShortInfo);
                            }
                        }
                    }
                }
            }
            return movies;
        }

        public static MovieInfo FindMovieInfoById(long movieId)
        {
            MovieInfo movieInfo = null;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionUtil.ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(MOVIE_INFO_COMMAND, connection))
                {
                    command.Parameters.AddWithValue("@movieId", movieId);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows && reader.Read())
                        {
                            movieInfo = new MovieInfo();
                            movieInfo.Id = long.Parse(reader["idMovie"].ToString());
                            movieInfo.Name = reader["strTitle"].ToString();
                            movieInfo.Tagline = reader["strTagLine"].ToString();
                            movieInfo.Description = reader["strPlot"].ToString();
                            movieInfo.IMDBRating = reader["fRating"].ToString().Replace(",", ".");
                            movieInfo.IMDBNumber = reader["IMDBID"].ToString();
                            movieInfo.IMDBVotes = reader["strVotes"].ToString();
                            movieInfo.Year = reader["iYear"].ToString();
                            movieInfo.Runtime = reader["runtime"].ToString();
                            movieInfo.MPAARating = reader["mpaa"].ToString();
                            movieInfo.Director = reader["strDirector"].ToString();
                            movieInfo.Writer = reader["strCredits"].ToString();

                            movieInfo.Picture = MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);
                            movieInfo.Thumb = MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);

                            using (SQLiteCommand actorsCommand = new SQLiteCommand(MOVIE_ACTORS_COMMAND, connection))
                            {
                                actorsCommand.Parameters.AddWithValue("@movieId", movieId);
                                using (SQLiteDataReader actorsReader = actorsCommand.ExecuteReader())
                                {
                                    if (actorsReader.HasRows)
                                    {
                                        while (actorsReader.Read())
                                        {
                                            Actor actor = new Actor();
                                            actor.Id = long.Parse(actorsReader["idActor"].ToString());
                                            actor.Name = actorsReader["strActor"].ToString();

                                            movieInfo.AddActor(actor);
                                        }
                                    }
                                }
                            }
                            using (SQLiteCommand genresCommand = new SQLiteCommand(MOVIE_GENRES_COMMAND, connection))
                            {
                                genresCommand.Parameters.AddWithValue("@movieId", movieId);
                                using (SQLiteDataReader genresReader = genresCommand.ExecuteReader())
                                {
                                    if (genresReader.HasRows)
                                    {
                                        while (genresReader.Read())
                                        {
                                            Genre genre = new Genre();
                                            genre.Id = long.Parse(genresReader["idGenre"].ToString());
                                            genre.Name = genresReader["strGenre"].ToString();

                                            movieInfo.AddGenre(genre);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return movieInfo;
        }

        public static void UpdateMovieInfo(MovieInfo movieInfo)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionUtil.ConnectionString))
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = DELETE_MOVIE_ACTORS_COMMAND;
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.ExecuteNonQuery();

                        foreach (Actor actor in movieInfo.Actors)
                        {
                            if (actor.Id == 0)
                            {
                                command.CommandText = INSERT_ACTOR_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", actor.Name);
                                actor.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                            command.Parameters.AddWithValue("@actorId", actor.Id);
                            command.ExecuteNonQuery();
                        }

                        command.CommandText = DELETE_MOVIE_GENRES_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.ExecuteNonQuery();

                        foreach (Genre genre in movieInfo.Genres)
                        {
                            if (genre.Id == 0)
                            {
                                command.CommandText = INSERT_GENRE_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", genre.Name);
                                genre.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_GENRE_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                            command.Parameters.AddWithValue("@genreId", genre.Id);
                            command.ExecuteNonQuery();
                        }

                        long directorId = 0;
                        command.CommandText = FIND_ACTOR_BY_NAME_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@name", movieInfo.Director);
                        object obj = command.ExecuteScalar();
                        if (obj == null)
                        {
                            command.CommandText = INSERT_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@name", movieInfo.Director);
                            directorId = long.Parse(command.ExecuteScalar().ToString());
                        }
                        else
                        {
                            directorId = long.Parse(obj.ToString());
                        }


                        command.CommandText = UPDATE_MOVIE_INFO_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.Parameters.AddWithValue("@title", movieInfo.Name);
                        command.Parameters.AddWithValue("@tagline", movieInfo.Tagline);
                        command.Parameters.AddWithValue("@directorId", directorId);
                        command.Parameters.AddWithValue("@writer", movieInfo.Writer);
                        command.Parameters.AddWithValue("@year", movieInfo.Year);
                        command.Parameters.AddWithValue("@runtime", movieInfo.Runtime);
                        command.Parameters.AddWithValue("@description", movieInfo.Description);
                        command.Parameters.AddWithValue("@imdbRating", movieInfo.IMDBRating);
                        command.Parameters.AddWithValue("@imdbNumber", movieInfo.IMDBNumber);
                        command.Parameters.AddWithValue("@imdbVotes", movieInfo.IMDBVotes);
                        command.Parameters.AddWithValue("@mpaaRating", movieInfo.MPAARating);

                        command.ExecuteNonQuery();

                        transaction.Commit();
                    }
                }
            }
        }

        public static List<Genre> FindAvailableGenres(long movieId)
        {
            List<Genre> genres = new List<Genre>();
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionUtil.ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(AVAIL_GENRES_COMMAND, connection))
                {
                    command.Parameters.AddWithValue("@movieId", movieId);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Genre genre = new Genre();
                                genre.Id = long.Parse(reader["idGenre"].ToString());
                                genre.Name = reader["strGenre"].ToString();

                                genres.Add(genre);
                            }
                        }
                    }
                }
            }
            return genres;
        }

        public static List<Actor> FindAvailableActors(long movieId)
        {
            List<Actor> actors = new List<Actor>();
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionUtil.ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(AVAIL_ACTORS_COMMAND, connection))
                {
                    command.Parameters.AddWithValue("@movieId", movieId);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Actor actor = new Actor();
                                actor.Id = long.Parse(reader["idActor"].ToString());
                                actor.Name = reader["strActor"].ToString();

                                actors.Add(actor);
                            }
                        }
                    }
                }
            }
            return actors;
        }

    }
}
